package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Measurement;
import model.Sensor;
import database.DBManager;

public class SensorDao {
	private String sqlread   	   = "SELECT * FROM sensor";
	private String sqlGetM   	   = "SELECT * FROM measurement WHERE sensorID = ?";
	private String sqlGetS   	   = "SELECT * FROM sensor WHERE sensorID = ?";
	private String sqlCreate   	   = "INSERT INTO sensor(name, unity, maxvalue) VALUES(?, ?, ?)";
	private String sqlUpdate	   = "UPDATE sensor SET sensor.maxvalue = ? WHERE sensor.sensorID = ?";
	
	
	private Connection        con      = null;
	private PreparedStatement psRead   = null;
	private PreparedStatement psGetM   = null;
	private PreparedStatement psGetS   = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psUpdate = null;

	public SensorDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			this.psRead   = con.prepareStatement(sqlread);
			this.psGetM   = con.prepareStatement(sqlGetM);
			this.psGetS   = con.prepareStatement(sqlGetS);
			this.psCreate = con.prepareStatement(sqlCreate);
			this.psUpdate = con.prepareStatement(sqlUpdate);
		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Sensor> getSensors(){
		ArrayList<Sensor> sensors = new ArrayList<Sensor>();
		try{
			ResultSet rs = psRead.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				String name = rs.getString(2);
				String unity = rs.getString(3);
				double maxvalue = rs.getDouble(4);
				ArrayList<Measurement> measurements = getMeasurements(sensorID);
				
				Sensor sensor = new Sensor(sensorID, name, unity, maxvalue, measurements);
				sensors.add(sensor);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return sensors;
	}	
	
	public ArrayList<Sensor> getSensorsWithoutMeasurements(){
		ArrayList<Sensor> sensors = new ArrayList<Sensor>();
		try{
			ResultSet rs = psRead.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				String name = rs.getString(2);
				String unity = rs.getString(3);
				double maxvalue = rs.getDouble(4);
				ArrayList<Measurement> measurements = new ArrayList<Measurement>();
				
				Sensor sensor = new Sensor(sensorID, name, unity, maxvalue, measurements);
				sensors.add(sensor);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return sensors;
	}
	
	public Sensor getSensorByID(int getSensorID){
		Sensor sensor = null;
		try{
			psGetS.clearParameters() ;
			psGetS.setInt(1, getSensorID);
			ResultSet rs = psGetS.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				String name = rs.getString(2);
				String unity = rs.getString(3);
				double maxvalue = rs.getDouble(4);

				ArrayList<Measurement> measurements = getMeasurements(sensorID);
				
				sensor = new Sensor(sensorID, name, unity, maxvalue, measurements);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return sensor;
	}
	
	public boolean createSensor(String name, String unity, double maxValue) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setString( 1, name );
			psCreate.setString( 2, unity );
			psCreate.setDouble( 3, maxValue );
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public boolean updateSensor(double maxvalue, int getSensorID) {
		boolean succeeded = false;
		try {
			psUpdate.clearParameters();
			psUpdate.setDouble( 1, maxvalue );
			psUpdate.setInt( 2,	getSensorID );
			if( psUpdate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	private ArrayList<Measurement> getMeasurements(int getSensorID){
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		try{
			psGetM.clearParameters() ;
			psGetM.setInt(1, getSensorID);
			ResultSet rs = psGetM.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				double value = rs.getDouble(3);
				
				Measurement measurement = new Measurement(sensorID, date, value);
				measurements.add(measurement);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurements;
	}
	
}
